﻿using System;
using System.Data.SQLite;

namespace CSharp大作业.Gobang.GameComponet.Store
{
    public class DB
    {
        public string dbpath = "Data Source=Storage\\data.db";
        public SQLiteCommand cmd;
        //缓冲区  将数据库数据读到内存   225=gameSize*gameSize
        public Step[] steps = new Step[225];
        public Game[] games = new Game[100];
        public DB()
        {
            SQLiteConnection con = new SQLiteConnection(dbpath);
            con.Open();
            cmd = new SQLiteCommand(con);
            cmd.CommandText = "CREATE TABLE IF NOT EXISTS Game(id int,name char(50),time char(256),steps int,primary key(id));";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "CREATE TABLE IF NOT EXISTS Step(id_game int,id int,player int,x int,y int,primary key(id_game,id));";
            cmd.ExecuteNonQuery();
            //Console.WriteLine("Created");
        }
        /// <summary>
        /// 调试函数，删表
        /// </summary>
        public void dropTable()
        {
            cmd.CommandText = "DROP TABLE IF EXISTS Game;";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "DROP TABLE IF EXISTS Step;";
            cmd.ExecuteNonQuery();
            // Console.WriteLine("droped");
        }
        /// <summary>
        /// 删除表内所有数据
        /// </summary>
        public void deleteAll()
        {
            cmd.CommandText = "DELETE FROM Game;";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "DELETE FROM Step;";
            cmd.ExecuteNonQuery();
            steps = new Step[225];
            games = new Game[100];
        }
        /// <summary>
        /// 删除表内所有数据
        /// </summary>
        public void deleteNullGame(int id_game)
        {
            cmd.CommandText = $"DELETE FROM Game WHERE steps='0' AND id<>{id_game};";
            cmd.Parameters.AddWithValue("@id_game", id_game);
            cmd.ExecuteNonQuery();
        }
        /// <summary>
        /// 向游戏表中插入一条数据
        /// </summary>
        public void insertGame(int id, string name, string time, int steps)
        {
            cmd.CommandText = "INSERT INTO Game(id,name,time,steps) VALUES(@id,@name,@time,@steps);";
            cmd.Parameters.AddWithValue("@id", id);
            cmd.Parameters.AddWithValue("@name", name);
            cmd.Parameters.AddWithValue("@time", time);
            cmd.Parameters.AddWithValue("@steps", steps);
            cmd.ExecuteNonQuery();
        }
        /// <summary>
        /// 更新游戏表中步数
        /// </summary>
        public void updateGame(int id, int steps)
        {
            cmd.CommandText = "UPDATE Game SET steps=@steps WHERE id=@id;";
            cmd.Parameters.AddWithValue("@steps", steps);
            cmd.Parameters.AddWithValue("@id", id);
            cmd.ExecuteNonQuery();
        }
        /// <summary>
        /// 向步数表中插入一条数据
        /// </summary>
        public void insertStep(int id_game, int id, int color, int x, int y)
        {
            cmd.CommandText = $"INSERT INTO Step(id_game,id,player,x,y) VALUES({id_game},{id},{color},{x},{y});";
            cmd.ExecuteNonQuery();
        }
        /// <summary>
        /// 获取当前游戏局数最大id
        /// </summary>
        public int getGameMaxID()
        {
            int i = 0;
            cmd.CommandText = "SELECT COUNT(*) FROM Game;";
            if (Convert.ToInt32(cmd.ExecuteScalar()) == 0) i = 0;
            else
            {
                cmd.CommandText = "SELECT MAX(id) FROM Game;";
                i = Convert.ToInt32(cmd.ExecuteScalar());
            }
            return i;
        }
        /// <summary>
        /// 获取本局游戏总步数，注意要+1
        /// </summary>
        public int getGamesteps(int id_game)
        {
            int i = -1;
            cmd.CommandText = $"SELECT COUNT(*) FROM Step WHERE id_game={id_game};";
            if (Convert.ToInt32(cmd.ExecuteScalar()) == 0)
            {
                // Console.WriteLine("步数："+(i+1)); 
                return i;
            }
            else
            {
                cmd.CommandText = $"SELECT steps FROM Game WHERE id={id_game};";
                i = Convert.ToInt32(cmd.ExecuteScalar());
                // Console.WriteLine("步数："+(i + 1));
                return i;
            }
        }
        /// <summary>
        /// 获取本局游戏所有数据
        /// </summary>
        public void getSteps(int id_game)
        {
            int count = 0;
            cmd.CommandText = $"SELECT * FROM Step WHERE id_game={id_game};";
            using (SQLiteDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    int id = reader.GetInt32(1);
                    int color = reader.GetInt32(2);
                    int x = reader.GetInt32(3);
                    int y = reader.GetInt32(4);
                    steps[count] = new Step(id_game, id, color, x, y);
                    count++;
                }
            }
        }

        /// <summary>
        /// 删除本局游戏数据
        /// </summary>
        public void deleteGame(int id_game)
        {
            cmd.CommandText = $"DELETE FROM Step WHERE id_game={id_game};";
            cmd.ExecuteNonQuery();
            cmd.CommandText = $"DELETE FROM Game WHERE id={id_game};";
            cmd.ExecuteNonQuery();
            steps = new Step[225];
            games = new Game[100];


        }
        /// <summary>
        /// 更新游戏表元组名称
        /// </summary>
        public void updateGameName(int id, string name)
        {
            cmd.CommandText = "SELECT COUNT(*) FROM Game WHERE id=@id;";
            cmd.Parameters.AddWithValue("@id", id);
            if (Convert.ToInt32(cmd.ExecuteScalar()) != 0)
            {
                cmd.CommandText = "UPDATE Game SET name=@name WHERE id=@id;";
                cmd.Parameters.AddWithValue("@name", name);
                cmd.Parameters.AddWithValue("@id", id);
                cmd.ExecuteNonQuery();
            }
        }
        /// <summary>
        /// 将数据库所有游戏局数据读入内存用games数组存储
        /// </summary>
        public void ReadGame()
        {
            int count = 0;
            cmd.CommandText = $"SELECT id,name,time FROM Game;";
            using (SQLiteDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    int id = reader.GetInt32(0);
                    string name = reader.GetString(1);
                    string time = reader.GetString(2);
                    games[count] = new Game(id, name, time);
                    count++;
                }
            }
        }
        /// <summary>
        /// 获取指定name的Game对象,并存储在缓冲区games
        /// </summary>
        public void getGame(string name)
        {
            int count = 0;
            for (int i = 0; i < 100; i++)
            {
                games[i] = null;
            }
            cmd.CommandText = "SELECT id,time FROM Game WHERE name=@name;";
            cmd.Parameters.AddWithValue("@name", name);
            cmd.ExecuteNonQuery();
            using (SQLiteDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    int id = reader.GetInt32(0);
                    string time = reader.GetString(1);
                    games[count] = new Game(id, name, time);
                    count++;
                }
            }
        }
    }
}
